const express = require('express');
const http = require('http');
const path = require('path'); // 添加path模块导入
const fs = require('fs'); // 添加fs模块导入
const cors = require('cors');
const { queryDatabase, executeTransaction, deleteDatabase } = require('./queue-client');
const { getAllSlots, getSlotById, bulkInsertSlots, updateSlot, deleteSlot, getEmptySlots } = require('./db-modules/slot-model');
const { getAllTasks, getTaskByTaskNo, insertTask, updateTask, deleteTask, checkTaskExists, updateTaskIssuedStatus } = require('./db-modules/task-model');
const { getUnissuedTasks } = require('./db-modules/unissued-tasks-model');
const { getStatistics, getLayerStatistics } = require('./db-modules/statistics-model');

const app = express();
const server = http.createServer(app);

// 中间件
app.use(cors());
app.use(express.json({ limit: '50mb' }));
app.use(express.urlencoded({ limit: '50mb', extended: true }));
app.use(express.static('public'));

// 统一错误处理中间件
app.use((error, req, res, next) => {
  console.error('未捕获的错误:', error);
  res.status(500).json({
    success: false,
    message: '服务器内部错误',
    error: process.env.NODE_ENV === 'development' ? error.message : undefined
  });
});

// 健康检查端点
app.get('/api/health', async (req, res) => {
  try {
    // 检查数据库连接
    const dbCheck = await queryDatabase('SELECT 1 as connected');
    
    res.json({
      success: true,
      message: '服务正常运行',
      timestamp: new Date().toISOString(),
      database: dbCheck ? 'connected' : 'disconnected'
    });
  } catch (error) {
    console.error('健康检查失败:', error);
    res.status(500).json({
      success: false,
      message: '健康检查失败',
      error: error.message
    });
  }
});

// 保存配置到文件
app.post('/api/config', async (req, res) => {
  try {
    const config = req.body;
    
    // 验证配置参数
    if (!config || typeof config !== 'object') {
      return res.status(400).json({
        success: false,
        message: '无效的配置数据'
      });
    }
    
    // 保存配置到文件
    const configPath = path.join(__dirname, '../public/warehouse-config.json');
    fs.writeFileSync(configPath, JSON.stringify(config, null, 2));
    
    console.log('✅ 配置文件已更新:', config);
    
    res.json({
      success: true,
      message: '配置文件保存成功',
      data: config
    });
  } catch (error) {
    console.error('保存配置文件失败:', error);
    res.status(500).json({
      success: false,
      message: '保存配置文件失败',
      error: error.message
    });
  }
});

// 读取配置文件
app.get('/api/config', async (req, res) => {
  try {
    const configPath = path.join(__dirname, '../public/warehouse-config.json');
    if (fs.existsSync(configPath)) {
      const configData = fs.readFileSync(configPath, 'utf8');
      const config = JSON.parse(configData);
      
      res.json({
        success: true,
        data: config
      });
    } else {
      // 返回默认配置
      res.json({
        success: true,
        data: { rows: 4, columns: 8, layers: 4, goodsRate: 70 }
      });
    }
  } catch (error) {
    console.error('读取配置文件失败:', error);
    res.status(500).json({
      success: false,
      message: '读取配置文件失败',
      error: error.message
    });
  }
});

// 重新初始化数据
app.post('/api/reinitialize', async (req, res) => {
  try {
    console.log('🔄 收到重新初始化请求');
    
    // 从配置文件读取最新配置
    const configPath = path.join(__dirname, '../public/warehouse-config.json');
    let config;
    
    if (fs.existsSync(configPath)) {
      const configData = fs.readFileSync(configPath, 'utf8');
      config = JSON.parse(configData);
    } else {
      // 无默认配置
      console.log('无默认配置');
    }
    
    console.log('使用配置:', config);
    
    // 生成货位数据
    const { generateWarehouseData } = require('./utils/warehouseData');
    const goodsRate = config.goodsRate !== undefined ? config.goodsRate : 70;
    const generatedData = generateWarehouseData(
      config.rows, 
      config.columns, 
      config.layers,
      goodsRate
    );
    
    console.log('生成的数据详情:', {
      rows: generatedData.rows,
      columns: generatedData.columns,
      layers: generatedData.layers,
      totalSlots: generatedData.totalSlots
    });
    
    // 直接初始化数据库（不通过队列）
    const { bulkInsertSlots } = require('./db-modules/slot-model');
    await bulkInsertSlots(Object.values(generatedData.slots));
    
    res.json({
      success: true,
      message: '数据重新初始化成功',
      data: {
        rows: config.rows,
        columns: config.columns,
        layers: config.layers,
        totalSlots: generatedData.totalSlots
      }
    });
  } catch (error) {
    console.error('重新初始化失败:', error);
    res.status(500).json({
      success: false,
      message: '重新初始化失败',
      error: error.message
    });
  }
});

// API路由

// 获取所有货位数据

// 启动服务器
const PORT = 3001;
server.listen(PORT, () => {
  console.log(`🚀 后端API服务器运行在端口 ${PORT}`);
  console.log(`📡 API端点: http://localhost:${PORT}/api`);
});
app.get('/api/slots', async (req, res) => {
  try {
    const rows = await getAllSlots();
    console.log('从getAllSlots获取到的数据:', rows);
    
    // 确保rows是数组
    const slotsArray = Array.isArray(rows) ? rows : [];
    
    // 转换数据格式
    const slots = {};
    const convertedSlotsArray = slotsArray.map(row => {
      const slot = {
        id: row.id,
        layer: row.layer,
        row: row.row,
        column: row.column,
        hasGoods: row.has_goods === 1,
        goodsInfo: row.goods_name ? {
          name: row.goods_name,
          quantity: row.goods_quantity,
          weight: row.goods_weight,
          entryDate: row.goods_entry_date
        } : null,
        position: {
          x: row.column,
          y: row.layer,
          z: row.row
        }
      };
      
      slots[row.id] = slot;
      return slot;
    });

    res.json({
      success: true,
      data: {
        slots,
        slotsArray: convertedSlotsArray
      }
    });
  } catch (error) {
    console.error('获取货位数据失败:', error);
    res.status(500).json({
      success: false,
      message: '获取货位数据失败',
      error: error.message
    });
  }
});

// 获取既无货又没有未下发任务的货位
app.get('/api/slots/available', async (req, res) => {
  try {
    console.log('收到获取可用货位的请求');
    const slots = await getAvailableSlots();
    console.log('获取到的可用货位数量:', slots.length);
    
    res.json({
      success: true,
      data: slots
    });
  } catch (error) {
    console.error('获取可用货位失败:', error);
    res.status(500).json({
      success: false,
      message: '获取可用货位失败',
      error: error.message
    });
  }
});

// 新增：获取无货货位（不考虑任务状态）
app.get('/api/slots/empty', async (req, res) => {
  try {
    console.log('收到获取无货货位的请求');
    const slots = await getEmptySlots();
    console.log('获取到的无货货位数量:', slots.length);
    
    res.json({
      success: true,
      data: slots
    });
  } catch (error) {
    console.error('获取无货货位失败:', error);
    res.status(500).json({
      success: false,
      message: '获取无货货位失败',
      error: error.message
    });
  }
});

// 获取单个货位详情
app.get('/api/slots/:slotId', async (req, res) => {
  try {
    const { slotId } = req.params;
    const row = await getSlotById(slotId);
    
    if (!row) {
      return res.status(404).json({
        success: false,
        message: '货位不存在'
      });
    }

    const slot = {
      id: row.id,
      layer: row.layer,
      row: row.row,
      column: row.column,
      hasGoods: row.has_goods === 1,
      goodsInfo: row.goods_name ? {
        name: row.goods_name,
        quantity: row.goods_quantity,
        weight: row.goods_weight,
        entryDate: row.goods_entry_date
      } : null
    };

    res.json({
      success: true,
      data: slot
    });
  } catch (error) {
    console.error('获取货位详情失败:', error);
    res.status(500).json({
      success: false,
      message: '获取货位详情失败',
      error: error.message
    });
  }
});

// 批量初始化货位数据
app.post('/api/slots/init', async (req, res) => {
  try {
    const { rows, columns, layers, slots } = req.body;
    
    console.log(`开始初始化货位数据: ${rows}排 × ${columns}列 × ${layers}层 = ${rows * columns * layers}个货位`);
    
    if (!slots || typeof slots !== 'object') {
      return res.status(400).json({
        success: false,
        message: '无效的货位数据'
      });
    }

    // 分批插入数据以避免内存问题
    const slotsArray = Object.values(slots);
    console.log(`总货位数: ${slotsArray.length}`);
    
    await bulkInsertSlots(slotsArray);

    res.json({
      success: true,
      message: `成功初始化 ${slotsArray.length} 个货位`,
      data: {
        rows,
        columns,
        layers,
        totalSlots: slotsArray.length
      }
    });
  } catch (error) {
    console.error('初始化货位数据失败:', error);
    res.status(500).json({
      success: false,
      message: '初始化货位数据失败',
      error: error.message
    });
  }
});

// 从SQL脚本加载数据（手动触发）
app.post('/api/load-data-from-script', async (req, res) => {
  try {
    console.log('🔍 收到从SQL脚本加载数据的请求');
    
    // 调用队列服务执行数据加载
    const result = await executeTransaction([
      {
        type: 'LOAD_DATA_FROM_SCRIPT',
        sql: '-- 从SQL脚本加载数据',
        params: []
      }
    ]);
    
    res.json({
      success: true,
      message: '数据加载请求已提交',
      data: result
    });
  } catch (error) {
    console.error('从SQL脚本加载数据失败:', error);
    res.status(500).json({
      success: false,
      message: '从SQL脚本加载数据失败',
      error: error.message
    });
  }
});

// 更新货位状态
app.put('/api/slots/:slotId', async (req, res) => {
  try {
    const { slotId } = req.params;
    const { hasGoods, goodsInfo } = req.body;

    await updateSlot(slotId, hasGoods, goodsInfo);

    res.json({
      success: true,
      message: '货位更新成功',
      data: {
        slotId,
        hasGoods,
        goodsInfo
      }
    });
  } catch (error) {
    console.error('更新货位失败:', error);
    res.status(500).json({
      success: false,
      message: '更新货位失败',
      error: error.message
    });
  }
});

// 获取统计信息
app.get('/api/statistics', async (req, res) => {
  try {
    const stats = await getStatistics();
    const layerStats = await getLayerStatistics();

    res.json({
      success: true,
      data: {
        totalSlots: stats.total_slots,
        occupiedSlots: stats.occupied_slots,
        emptySlots: stats.empty_slots,
        occupancyRate: stats.total_slots > 0 
          ? ((stats.occupied_slots / stats.total_slots) * 100).toFixed(2)
          : '0.00',
        layerStatistics: layerStats.map(layer => ({
          layer: layer.layer,
          total: layer.total,
          occupied: layer.occupied,
          empty: layer.empty,
          occupancyRate: layer.total > 0
            ? ((layer.occupied / layer.total) * 100).toFixed(2)
            : '0.00'
        }))
      }
    });
  } catch (error) {
    console.error('获取统计信息失败:', error);
    res.status(500).json({
      success: false,
      message: '获取统计信息失败',
      error: error.message
    });
  }
});

// TaskList API端点

// 获取所有任务记录
app.get('/api/tasks', async (req, res) => {
  try {
    const tasks = await getAllTasks();
    
    res.json({
      success: true,
      data: tasks
    });
  } catch (error) {
    console.error('获取任务记录失败:', error);
    res.status(500).json({
      success: false,
      message: '获取任务记录失败',
      error: error.message
    });
  }
});

// 获取未下发的任务
app.get('/api/tasks/unissued', async (req, res) => {
  try {
    console.log('🔍 收到获取未下发任务的请求');
    // 获取未下发的任务
    const tasks = await getUnissuedTasks();
    
    console.log(`✅ 返回 ${tasks.length} 条未下发任务`);
    res.json({
      success: true,
      data: tasks,
      timestamp: new Date().toISOString()
    });
  } catch (error) {
    console.error('获取未下发任务失败:', error);
    res.status(500).json({
      success: false,
      message: '获取未下发任务失败',
      error: error.message
    });
  }
});

// 根据任务号获取任务记录
app.get('/api/tasks/:taskNo', async (req, res) => {
  try {
    const { taskNo } = req.params;
    const tasks = await getTaskByTaskNo(taskNo);
    
    res.json({
      success: true,
      data: tasks
    });
  } catch (error) {
    console.error('获取任务记录失败:', error);
    res.status(500).json({
      success: false,
      message: '获取任务记录失败',
      error: error.message
    });
  }
});

// 插入任务记录
app.post('/api/tasks', async (req, res) => {
  try {
    const taskData = req.body;
    
    // 验证必要字段
    if (!taskData.Unload_Line || !taskData.Unload_Col || !taskData.Unload_Layer) {
      return res.status(400).json({
        success: false,
        message: '缺少必要的字段: Unload_Line, Unload_Col, Unload_Layer'
      });
    }  
   
    // 插入任务记录
    const result = await insertTask(taskData);

    if (result.success) {
      res.json({
        success: true,
        message: '任务记录插入成功',
        taskId: result.taskId
      });
    } else {
      res.status(500).json({
        success: false,
        message: result.message
      });
    }
  } catch (error) {
    console.error('插入任务记录失败:', error);
    res.status(500).json({
      success: false,
      message: '插入任务记录失败',
      error: error.message
    });
  }
});



// 更新任务记录
app.put('/api/tasks/:id', async (req, res) => {
  try {
    const { id } = req.params;
    const taskData = req.body;
    await updateTask(id, taskData);
    
    res.json({
      success: true,
      message: '任务记录更新成功',
      data: {
        id: id,
        ...taskData
      }
    });
  } catch (error) {
    console.error('更新任务记录失败:', error);
    res.status(500).json({
      success: false,
      message: '更新任务记录失败',
      error: error.message
    });
  }
});

// 更新任务下发状态
app.put('/api/tasks/:id/issued', async (req, res) => {
  try {
    const { id } = req.params;
    const { is_issued } = req.body;
    await updateTaskIssuedStatus(id, is_issued);
    
    res.json({
      success: true,
      message: `任务${is_issued ? '已标记为已下发' : '已标记为未下发'}`
    });
  } catch (error) {
    console.error('更新任务下发状态失败:', error);
    res.status(500).json({
      success: false,
      message: '更新任务下发状态失败',
      error: error.message
    });
  }
});

// 通用表数据查询API端点
app.get('/api/table/:tableName', async (req, res) => {
  // 将tableName提取到try块外部，以便在catch块中也能访问
  const { tableName } = req.params;
  
  try {
    console.log(`收到查询表数据请求: ${tableName}`);
    
    // 验证表名是否允许查询
    const allowedTables = ['storage_slots', 'TaskList', 'goods_info', 'tasklist_audit'];
    if (!allowedTables.includes(tableName)) {
      return res.status(400).json({
        success: false,
        message: `不允许查询表: ${tableName}`
      });
    }
    
    // 构建查询SQL
    const sql = `SELECT * FROM ${tableName}`;
    
    // 通过队列服务执行查询
    const rows = await queryDatabase(sql);
    
    res.json({
      success: true,
      data: rows,
      tableName: tableName,
      count: rows.length
    });
  } catch (error) {
    console.error(`查询表 ${tableName} 数据失败:`, error);
    res.status(500).json({
      success: false,
      message: `查询表 ${tableName} 数据失败`,
      error: error.message,
      stack: error.stack  // 添加错误堆栈信息以便调试
    });
  }
});

// 插入无货货位任务记录
app.post('/api/tasks/empty-slot', async (req, res) => {
  try {
    const { Unload_Line, Unload_Col, Unload_Layer } = req.body;
    
    // 验证参数
    if (!Unload_Line || !Unload_Col || !Unload_Layer) {
      return res.status(400).json({
        success: false,
        message: '缺少必要的参数: Unload_Line, Unload_Col, Unload_Layer'
      });
    }
    
    // 检查该货位是否已在TaskList中存在且未下发
    const exists = await checkTaskExists(Unload_Line, Unload_Col, Unload_Layer);
    // 这里需要直接查询数据库，因为我们要在插入前检查

    // 生成随机任务号和作业类型
    const Task_No = Math.floor(Math.random() * 10000) + 1;
    const Job_Style = Math.floor(Math.random() * 3) + 1; // 1-3之间的随机数
    
    // 准备任务数据 (直接使用1基索引)
    const taskData = {
      Task_No,
      Job_Style,
      Unload_Line: parseInt(Unload_Line),   // 使用1基索引
      Unload_Col: parseInt(Unload_Col),     // 使用1基索引
      Unload_Layer: parseInt(Unload_Layer), // 使用1基索引
      is_issued: 0
    };
    
    // 插入任务记录
    const result = await insertTask(taskData);
    
    if (result.success) {
      res.json({
        success: true,
        message: result.message,
        taskId: result.taskId
      });
    } else {
      res.status(500).json({
        success: false,
        message: result.message
      });
    }
  } catch (error) {
    console.error('插入无货货位任务记录失败:', error);
    res.status(500).json({
      success: false,
      message: '插入无货货位任务记录失败',
      error: error.message
    });
  }
});

// 自动查询无货货位并插入到任务列表
app.post('/api/tasks/auto-insert-empty-slots', async (req, res) => {
  try {
    console.log('收到自动插入无货货位任务请求');
    
    // 获取所有无货货位（不考虑任务状态）
    const emptySlots = await getEmptySlots();
    console.log(`找到 ${emptySlots.length} 个无货货位`);
    
    if (emptySlots.length === 0) {
      return res.json({
        success: true,
        message: '没有找到无货货位',
        insertedCount: 0
      });
    }
    
    // 查找第一个可以插入任务列表的无货货位
    for (const slot of emptySlots) {
      // 检查该货位是否已在TaskList中存在
      const exists = await checkTaskExists(slot.row, slot.column, slot.layer);
      // 这里需要直接查询数据库，因为我们要在插入前检查
      
      // 生成随机任务号和作业类型
      const Task_No = Math.floor(Math.random() * 10000) + 1;
      const Job_Style = Math.floor(Math.random() * 3) + 1; // 1-3之间的随机数
      
      // 准备任务数据 (使用1基索引)
      const taskData = {
        Task_No,
        Job_Style,
        Unload_Line: slot.row,    // 使用1基索引
        Unload_Col: slot.column,  // 使用1基索引
        Unload_Layer: slot.layer, // 使用1基索引
        is_issued: 0
      };
      
      // 插入任务记录
      const result = await insertTask(taskData);
      
      if (result.success) {
        console.log(`成功插入货位任务: 排${slot.row}, 列${slot.column}, 层${slot.layer}`);
        return res.json({
          success: true,
          message: `成功插入货位任务: 排${slot.row}, 列${slot.column}, 层${slot.layer}`,
          insertedCount: 1,
          insertedSlot: {
            row: slot.row,
            column: slot.column,
            layer: slot.layer
          }
        });
      } else {
        return res.status(500).json({
          success: false,
          message: `插入货位任务失败: ${result.message}`,
          insertedCount: 0
        });
      }
    }
    
    // 如果所有货位都不能插入
    return res.json({
      success: true,
      message: '没有可插入的货位（所有无货货位都存在未下发任务）',
      insertedCount: 0
    });
  } catch (error) {
    console.error('自动插入无货货位任务失败:', error);
    res.status(500).json({
      success: false,
      message: '自动插入无货货位任务失败',
      error: error.message
    });
  }
});

// 自动查询无货货位（仅查询，不插入）
app.post('/api/tasks/auto-query-empty-slots', async (req, res) => {
  try {
    console.log('收到自动查询无货货位请求');
    
    // 获取所有无货货位（不考虑任务状态）
    const emptySlots = await getEmptySlots();
    console.log(`找到 ${emptySlots.length} 个无货货位`);
    
    if (emptySlots.length === 0) {
      return res.json({
        success: true,
        message: '没有找到无货货位',
        foundCount: 0
      });
    }
    
    // 按照排、列、层排序，确保一致性
    emptySlots.sort((a, b) => {
      if (a.row !== b.row) return a.row - b.row;
      if (a.column !== b.column) return a.column - b.column;
      return a.layer - b.layer;
    });
    
    // 查找第一个可以插入任务列表的无货货位
    for (const slot of emptySlots) {
      // 检查该货位是否已在TaskList中存在未下发任务
      const exists = await checkTaskExists(slot.row, slot.column, slot.layer);
      
      // 如果不存在未下发任务，则返回该货位
      // exists为true表示货位满足条件（无货且无未下发任务），可以使用
      if (exists) {
        return res.json({
          success: true,
          message: `找到可插入的货位: 排${slot.row}, 列${slot.column}, 层${slot.layer}`,
          foundCount: 1,
          foundSlot: {
            row: slot.row,
            column: slot.column,
            layer: slot.layer
          }
        });
      }
    }
    
    // 如果所有货位都存在未下发任务
    return res.json({
      success: true,
      message: '没有可插入的货位（所有无货货位都存在未下发任务）',
      foundCount: 0
    });
  } catch (error) {
    console.error('自动查询无货货位失败:', error);
    res.status(500).json({
      success: false,
      message: '自动查询无货货位失败',
      error: error.message
    });
  }
});

// 获取任务审计记录
app.get('/api/tasks/audit/:taskId?', async (req, res) => {
  try {
    const { taskId } = req.params;
    
    let query = `
      SELECT 
        a.*,
        t.Task_No,
        t.Unload_Line,
        t.Unload_Col,
        t.Unload_Layer
      FROM tasklist_audit a
      LEFT JOIN TaskList t ON a.task_id = t.id
    `;
    
    let params = [];
    
    if (taskId) {
      query += ` WHERE a.task_id = ? ORDER BY a.changed_at DESC`;
      params = [taskId];
    } else {
      query += ` ORDER BY a.changed_at DESC LIMIT 100`;
    }
    
    // 通过队列服务执行审计查询
    const rows = await queryDatabase(query, params);
    
    res.json({
      success: true,
      data: rows
    });
  } catch (error) {
    console.error('获取任务审计记录失败:', error);
    res.status(500).json({
      success: false,
      message: '获取任务审计记录失败',
      error: error.message
    });
  }
});

// 删除任务记录
app.delete('/api/tasks/:id', async (req, res) => {
  try {
    const { id } = req.params;
    await deleteTask(id);
    
    res.json({
      success: true,
      message: '任务记录删除成功'
    });
  } catch (error) {
    console.error('删除任务记录失败:', error);
    res.status(500).json({
      success: false,
      message: '删除任务记录失败',
      error: error.message
    });
  }
});

// 清空TaskList表
app.delete('/api/tasks/clear', async (req, res) => {
  try {
    const sql = 'DELETE FROM TaskList';
    await deleteDatabase(sql);
    
    res.json({
      success: true,
      message: 'TaskList表已清空'
    });
  } catch (error) {
    console.error('清空TaskList表失败:', error);
    res.status(500).json({
      success: false,
      message: '清空TaskList表失败',
      error: error.message
    });
  }
});

// 复杂查询API端点 - 支持过滤和联表查询
app.get('/api/query', async (req, res) => {
  try {
    const { table, join, filter, fields } = req.query;
    
    // 验证参数
    if (!table) {
      return res.status(400).json({
        success: false,
        message: '缺少必要的参数: table'
      });
    }
    
    // 构建查询SQL
    let sql = `SELECT ${fields || '*'} FROM ${table}`;
    
    // 添加JOIN子句
    if (join) {
      sql += ` ${join}`;
    }
    
    // 添加WHERE条件
    const whereConditions = [];
    const queryParams = [];
    
    // 处理过滤条件
    if (filter) {
      try {
        const filterObj = JSON.parse(decodeURIComponent(filter));
        for (const [key, value] of Object.entries(filterObj)) {
          whereConditions.push(`${key} = ?`);
          queryParams.push(value);
        }
      } catch (e) {
        // 如果不是JSON格式，按字符串处理
        whereConditions.push(filter);
      }
    }
    
    if (whereConditions.length > 0) {
      sql += ` WHERE ${whereConditions.join(' AND ')}`;
    }
    
    console.log('执行复杂查询:', sql, queryParams);
    
    // 通过队列服务执行复杂查询
    const rows = await queryDatabase(sql, queryParams);
    
    res.json({
      success: true,
      data: rows,
      query: sql,
      count: rows.length
    });
  } catch (error) {
    console.error('复杂查询失败:', error);
    res.status(500).json({
      success: false,
      message: '复杂查询失败',
      error: error.message
    });
  }
});

// 删除货位记录
app.delete('/api/slots/:id', async (req, res) => {
  try {
    const { id } = req.params;
    await deleteSlot(id);
    
    res.json({
      success: true,
      message: '货位记录删除成功'
    });
  } catch (error) {
    console.error('删除货位记录失败:', error);
    res.status(500).json({
      success: false,
      message: '删除货位记录失败',
      error: error.message
    });
  }
});

// 检查指定排、列、层的货位是否已在TaskList中存在且未下发
app.get('/api/tasks/check', async (req, res) => {
  try {
    const { row, col, layer } = req.query;
    
    // 验证参数
    if (!row || !col || !layer) {
      return res.status(400).json({
        success: false,
        message: '缺少必要的参数: row, col, layer'
      });
    }
    
    // 检查该货位是否已在TaskList中存在且未下发
    const exists = await checkTaskExists(row, col, layer);
    
    if (exists) {
      // 获取任务详情
      const checkSql = fs.readFileSync(path.join(__dirname, 'check-task-exists.sql'), 'utf8');
      const checkParams = [row, col, layer];
      const tasks = await queryDatabase(checkSql, checkParams);
      
      res.json({
        success: true,
        exists: true,
        is_issued: tasks[0].is_issued,
        task: tasks[0]
      });
    } else {
      res.json({
        success: true,
        exists: false,
        is_issued: null,
        task: null
      });
    }
  } catch (error) {
    console.error('检查任务是否存在失败:', error);
    res.status(500).json({
      success: false,
      message: '检查任务是否存在失败',
      error: error.message
    });
  }
});

// 测试check-task-exists.sql查询
app.get('/api/test-check-task', async (req, res) => {
  try {
    const row = 1;
    const col = 1;
    const layer = 2;
    
    const checkSql = fs.readFileSync(path.join(__dirname, 'check-task-exists.sql'), 'utf8');
    const checkParams = [row, col, layer];
    const result = await queryDatabase(checkSql, checkParams);
    
    res.json({
      success: true,
      data: result,
      query: checkSql,
      params: checkParams
    });
  } catch (error) {
    console.error('测试check-task-exists.sql查询失败:', error);
    res.status(500).json({
      success: false,
      message: '测试check-task-exists.sql查询失败',
      error: error.message
    });
  }
});

// 调试API端点：检查前几个无货货位
app.get('/api/debug-empty-slots', async (req, res) => {
  try {
    // 获取所有无货货位（不考虑任务状态）
    const emptySlots = await getEmptySlots();
    console.log(`找到 ${emptySlots.length} 个无货货位`);
    
    // 只检查前5个货位
    const slotsToCheck = emptySlots.slice(0, 5);
    const results = [];
    
    for (const slot of slotsToCheck) {
      // 检查该货位是否已在TaskList中存在未下发任务
      const exists = await checkTaskExists(slot.row, slot.column, slot.layer);
      results.push({
        slot: {
          row: slot.row,
          column: slot.column,
          layer: slot.layer
        },
        hasUnissuedTask: exists
      });
    }
    
    res.json({
      success: true,
      data: results
    });
  } catch (error) {
    console.error('调试无货货位失败:', error);
    res.status(500).json({
      success: false,
      message: '调试无货货位失败',
      error: error.message
    });
  }
});

// 测试直接查询TaskList表
app.get('/api/test-tasklist', async (req, res) => {
  try {
    const row = 1;
    const col = 2;
    const layer = 1;
    
    const checkSql = `
      SELECT * 
      FROM TaskList 
      WHERE Unload_Line = ? 
        AND Unload_Col = ? 
        AND Unload_Layer = ? 
        AND is_issued = 0
    `;
    const checkParams = [row, col, layer];
    const result = await queryDatabase(checkSql, checkParams);
    
    res.json({
      success: true,
      data: result,
      query: checkSql,
      params: checkParams
    });
  } catch (error) {
    console.error('测试TaskList查询失败:', error);
    res.status(500).json({
      success: false,
      message: '测试TaskList查询失败',
      error: error.message
    });
  }
});

// 健康检查
app.get('/api/health', async (req, res) => {
  try {
    // 检查队列服务是否可用
    const healthCheckSql = 'SELECT 1 as status';
    const result = await queryDatabase(healthCheckSql);
    
    res.json({
      success: true,
      message: '服务正常运行',
      timestamp: new Date().toISOString(),
      database: result.length > 0 ? 'connected' : 'disconnected',
      services: {
        queueService: 'ok',
        databaseService: 'ok'
      }
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      message: '服务健康检查失败',
      timestamp: new Date().toISOString(),
      error: error.message
    });
  }
});

// 测试路由
app.get('/api/test', (req, res) => {
  console.log('收到测试请求');
  res.json({
    success: true,
    message: '测试路由工作正常'
  });
});

// 提供sql.js的初始化脚本
app.get('/sql.js', (req, res) => {
  res.sendFile(require.resolve('sql.js/dist/sql-wasm.js'));
});

module.exports = app;